Imports System.Text
Imports System.Data.SqlClient
Imports System.Data
Imports Ocean.OceanFramework.SQLDataAccess.DataAccessConstants

Namespace SQLDataAccess

    ''' <summary>
    ''' Contains helper methods related to data access
    ''' </summary>
    ''' <remarks></remarks>
    Public NotInheritable Class DataAccessHelper

#Region " Constructors "

        Private Sub New()
        End Sub

#End Region

#Region " Public Helper Routines "

        Public Shared Function BuildSQLParameter(ByVal strParameterName As String, ByVal objSqlDbType As System.Data.SqlDbType, ByVal intSize As Integer, ByVal bolNullable As Boolean, ByVal objDirection As System.Data.ParameterDirection) As SqlParameter

            Dim obj As New SqlParameter(strParameterName, objSqlDbType)
            obj.Direction = objDirection
            obj.Size = intSize
            obj.IsNullable = bolNullable
            Return obj
        End Function

        Public Shared Function BuildSQLParameter(ByVal strParameterName As String, ByVal objSqlDbType As System.Data.SqlDbType, ByVal intSize As Integer, ByVal objDirection As System.Data.ParameterDirection) As SqlParameter

            Dim obj As New SqlParameter(strParameterName, objSqlDbType)
            obj.Direction = objDirection
            obj.Size = intSize
            Return obj
        End Function

        Public Shared Function BuildSQLParameter(ByVal strParameterName As String, ByVal objSqlDbType As System.Data.SqlDbType, ByVal intSize As Integer, ByVal objDirection As System.Data.ParameterDirection, ByVal bolNullable As Boolean, ByVal objValue As Object) As SqlParameter

            Dim obj As New SqlParameter(strParameterName, objSqlDbType)
            obj.Direction = objDirection
            obj.Size = intSize
            obj.IsNullable = bolNullable
            obj.Value = objValue
            Return obj
        End Function

        Public Shared Function BuildSQLParameter(ByVal strParameterName As String, ByVal objSqlDbType As System.Data.SqlDbType, ByVal intSize As Integer, ByVal objDirection As System.Data.ParameterDirection, ByVal objValue As Object) As SqlParameter

            Dim obj As New SqlParameter(strParameterName, objSqlDbType)
            obj.Direction = objDirection
            obj.Size = intSize
            obj.Value = objValue
            Return obj
        End Function

        ''' <summary>
        ''' Used to build an outbound parameter since the value is not passed
        ''' </summary>
        ''' <param name="strParameterName">Name of SQL parameter</param>
        ''' <param name="objSqlDbType">Date type of SQL parameter</param>
        ''' <param name="objDirection">Direction of SQL parameter, normally Outbound</param>
        ''' <returns>SQL Parameter</returns>
        Public Shared Function BuildSQLParameter(ByVal strParameterName As String, ByVal objSqlDbType As System.Data.SqlDbType, ByVal objDirection As System.Data.ParameterDirection) As SqlParameter

            Dim obj As New SqlParameter(strParameterName, objSqlDbType)
            obj.Direction = objDirection
            Return obj
        End Function

        Public Shared Function BuildSQLParameter(ByVal strParameterName As String, ByVal objSqlDbType As System.Data.SqlDbType, ByVal objDirection As System.Data.ParameterDirection, ByVal bolNullable As Boolean, ByVal objValue As Object) As SqlParameter

            Dim obj As New SqlParameter(strParameterName, objSqlDbType)
            obj.Direction = objDirection
            obj.IsNullable = bolNullable
            obj.Value = objValue
            Return obj
        End Function

        Public Shared Function BuildSQLParameter(ByVal strParameterName As String, ByVal objSqlDbType As System.Data.SqlDbType, ByVal objDirection As System.Data.ParameterDirection, ByVal objValue As Object) As SqlParameter

            Dim obj As New SqlParameter(strParameterName, objSqlDbType)
            obj.Direction = objDirection
            obj.Value = objValue
            Return obj
        End Function

        Public Shared Function BuildSQLParameterReturnCode(Optional ByVal strParameterName As String = G_STRING_RETURN_CODE) As SqlParameter

            Dim obj As New System.Data.SqlClient.SqlParameter(strParameterName, SqlDbType.Int)
            obj.Direction = ParameterDirection.Output
            Return obj
        End Function

        Public Shared Function BuildSQLParameterReturnValue(Optional ByVal strParameterName As String = G_STRING_RETURN_VALUE) As SqlParameter

            Dim obj As New System.Data.SqlClient.SqlParameter(strParameterName, SqlDbType.Int)
            obj.Direction = ParameterDirection.ReturnValue
            Return obj
        End Function

        Public Shared Function ParseDatabaseNameFromConnectionString(ByVal strConnectionString As String) As String

            Dim strDataBaseName As String = String.Empty
            Dim aryTemp() As String = strConnectionString.Split(";"c)

            For Each s As String In aryTemp

                If s.ToLower.StartsWith("initial catalog") OrElse s.ToLower.StartsWith("database") Then

                    Dim aryTemp2() As String = s.Split("="c)
                    strDataBaseName = aryTemp2(1)
                End If

            Next

            If strDataBaseName = String.Empty Then
                Throw New ArgumentOutOfRangeException("ConnecionString", strConnectionString, "Could not parse database name from connection string.")
            End If

            Return strDataBaseName
        End Function

        ''' <summary>
        ''' Parses a SQL Client Exception by iterating through the collection of errors inside the exception, returning a string representing all the exceptions in the collection.
        ''' </summary>
        ''' <param name="eX">SQL Client Exception</param>
        ''' <param name="strNewLineCharacter">New line string defaults to CRLF.  This parameter allows the passing of HTML based new line characters if required.</param>
        ''' <returns>String representing the exceptions in the collection.</returns>
        ''' <remarks></remarks>
        Public Shared Function ParseSQLException(ByVal eX As SqlException, Optional ByVal strNewLineCharacter As String = ControlChars.NewLine) As String

            Dim sb As StringBuilder = New StringBuilder(1024)
            sb.Append(strNewLineCharacter)
            sb.Append(strNewLineCharacter)

            For intX As Integer = 0 To eX.Errors.Count - 1
                sb.Append("Index #" & intX.ToString() & strNewLineCharacter)
                sb.Append("Server: " & eX.Errors(intX).Server & strNewLineCharacter)
                sb.Append("Error Number: " & eX.Errors(intX).Number & strNewLineCharacter)
                sb.Append("Message: " & eX.Errors(intX).Message & strNewLineCharacter)
                sb.Append("Severity: " & eX.Errors(intX).Class & strNewLineCharacter)
                sb.Append("State: " & eX.Errors(intX).State & strNewLineCharacter)
                sb.Append("Source: " & eX.Errors(intX).Source & strNewLineCharacter)
                sb.Append("Procedure: " & eX.Errors(intX).Procedure & strNewLineCharacter)
                sb.Append("LineNumber: " & eX.Errors(intX).LineNumber & strNewLineCharacter)

                For Each de As DictionaryEntry In eX.Data
                    sb.AppendFormat("Data : {0} : {1}", de.Key, de.Value)
                Next

                sb.Append(strNewLineCharacter)
            Next intX

            Return sb.ToString
        End Function

#End Region

    End Class

End Namespace
